{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "98a2d6a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Py3_Jupyter_Nb_讯捷集团_零售需求(主机量规律)_GF_2024-01-15.ipynb\n",
    "# Create By GF 2024-01-15 11:56"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c753a97d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import zhdate\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "30d2b2bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "StoreQtyPDF = pd.read_csv(\"./Datas/CSV数据_讯捷集团_销量数据_年各部门主机销量_2022-01-01至2023-12-31.csv\")\n",
    "# ..................................................\n",
    "CalendarPDF = pd.read_excel(\"./Datas/日历_2020-2024_节日及事件_GF_2024-01-04.xlsx\", sheet_name=\"Sheet1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f2a16481",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>部门</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>体系</th>\n",
       "      <th>日期</th>\n",
       "      <th>主机销量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>二部</td>\n",
       "      <td>四川成都184号形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2022-1-18</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>二部</td>\n",
       "      <td>四川成都184号形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2022-1-21</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>二部</td>\n",
       "      <td>四川成都184号形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2022-1-24</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>二部</td>\n",
       "      <td>四川成都184号形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2022-5-17</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>二部</td>\n",
       "      <td>四川成都184号形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2022-5-20</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160684</th>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆鱼洞步行街形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2023-6-21</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160685</th>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆鱼洞步行街形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2023-6-24</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160686</th>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆鱼洞步行街形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2023-10-15</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160687</th>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆鱼洞步行街形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2023-10-18</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160688</th>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆鱼洞步行街形象店</td>\n",
       "      <td>形象</td>\n",
       "      <td>2023-10-21</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160689 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        部门         门店名称  体系          日期  主机销量\n",
       "0       二部  四川成都184号形象店  形象   2022-1-18    25\n",
       "1       二部  四川成都184号形象店  形象   2022-1-21    35\n",
       "2       二部  四川成都184号形象店  形象   2022-1-24    64\n",
       "3       二部  四川成都184号形象店  形象   2022-5-17     7\n",
       "4       二部  四川成都184号形象店  形象   2022-5-20    18\n",
       "...     ..          ...  ..         ...   ...\n",
       "160684  重庆   重庆鱼洞步行街形象店  形象   2023-6-21    10\n",
       "160685  重庆   重庆鱼洞步行街形象店  形象   2023-6-24    10\n",
       "160686  重庆   重庆鱼洞步行街形象店  形象  2023-10-15     5\n",
       "160687  重庆   重庆鱼洞步行街形象店  形象  2023-10-18     2\n",
       "160688  重庆   重庆鱼洞步行街形象店  形象  2023-10-21     4\n",
       "\n",
       "[160689 rows x 5 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "StoreQtyPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f0d70d45",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>节日名称</th>\n",
       "      <th>农历</th>\n",
       "      <th>农历年份</th>\n",
       "      <th>农历月日</th>\n",
       "      <th>是否闰月</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>731</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>元旦</td>\n",
       "      <td>农历2021年11月29日</td>\n",
       "      <td>2021</td>\n",
       "      <td>11-29</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>732</th>\n",
       "      <td>2022-01-02</td>\n",
       "      <td>元旦</td>\n",
       "      <td>农历2021年11月30日</td>\n",
       "      <td>2021</td>\n",
       "      <td>11-30</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>733</th>\n",
       "      <td>2022-01-03</td>\n",
       "      <td>元旦</td>\n",
       "      <td>农历2021年12月1日</td>\n",
       "      <td>2021</td>\n",
       "      <td>12-01</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>734</th>\n",
       "      <td>2022-01-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2021年12月2日</td>\n",
       "      <td>2021</td>\n",
       "      <td>12-02</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>735</th>\n",
       "      <td>2022-01-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2021年12月3日</td>\n",
       "      <td>2021</td>\n",
       "      <td>12-03</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1822</th>\n",
       "      <td>2024-12-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2024年11月27日</td>\n",
       "      <td>2024</td>\n",
       "      <td>11-27</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1823</th>\n",
       "      <td>2024-12-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2024年11月28日</td>\n",
       "      <td>2024</td>\n",
       "      <td>11-28</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1824</th>\n",
       "      <td>2024-12-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2024年11月29日</td>\n",
       "      <td>2024</td>\n",
       "      <td>11-29</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1825</th>\n",
       "      <td>2024-12-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2024年11月30日</td>\n",
       "      <td>2024</td>\n",
       "      <td>11-30</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1826</th>\n",
       "      <td>2024-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2024年12月1日</td>\n",
       "      <td>2024</td>\n",
       "      <td>12-01</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1096 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             日期 节日名称             农历  农历年份   农历月日 是否闰月\n",
       "731  2022-01-01   元旦  农历2021年11月29日  2021  11-29    否\n",
       "732  2022-01-02   元旦  农历2021年11月30日  2021  11-30    否\n",
       "733  2022-01-03   元旦   农历2021年12月1日  2021  12-01    否\n",
       "734  2022-01-04  NaN   农历2021年12月2日  2021  12-02    否\n",
       "735  2022-01-05  NaN   农历2021年12月3日  2021  12-03    否\n",
       "...         ...  ...            ...   ...    ...  ...\n",
       "1822 2024-12-27  NaN  农历2024年11月27日  2024  11-27    否\n",
       "1823 2024-12-28  NaN  农历2024年11月28日  2024  11-28    否\n",
       "1824 2024-12-29  NaN  农历2024年11月29日  2024  11-29    否\n",
       "1825 2024-12-30  NaN  农历2024年11月30日  2024  11-30    否\n",
       "1826 2024-12-31  NaN   农历2024年12月1日  2024  12-01    否\n",
       "\n",
       "[1096 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 日期转换。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = CalendarPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 数据筛选。\n",
    "# --------------------------------------------------\n",
    "ZhCalendarPDF = SourceDataPDF[[\"日期\", \"节日名称\"]]\n",
    "ZhCalendarPDF = ZhCalendarPDF[(\"2022-01-01\" <= ZhCalendarPDF[\"日期\"]) & (ZhCalendarPDF[\"日期\"] <= \"2024-12-31\")]\n",
    "\n",
    "# 类型转换。\n",
    "# --------------------------------------------------\n",
    "ZhCalendarPDF[\"日期\"] = ZhCalendarPDF[\"日期\"].astype(\"datetime64[ns]\")\n",
    "\n",
    "# 计算字段。\n",
    "# --------------------------------------------------\n",
    "ZhCalendarPDF[\"农历\"] = ZhCalendarPDF[\"日期\"].apply(lambda X: zhdate.ZhDate.from_datetime(X))\n",
    "# ..................................................\n",
    "ZhCalendarPDF[\"农历年份\"] = ZhCalendarPDF[\"农历\"].apply(lambda X: \"%d\" % X.lunar_year)\n",
    "ZhCalendarPDF[\"农历月日\"] = ZhCalendarPDF[\"农历\"].apply(lambda X: \"%02d-%02d\" % (X.lunar_month, X.lunar_day))\n",
    "ZhCalendarPDF[\"是否闰月\"] = ZhCalendarPDF[\"农历\"].apply(lambda X: \"是\" if (X.leap_month == True) else \"否\")\n",
    "\n",
    "# ##################################################\n",
    "ZhCalendarPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c2ac1faf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>节日名称</th>\n",
       "      <th>农历</th>\n",
       "      <th>农历年份</th>\n",
       "      <th>农历月日</th>\n",
       "      <th>是否闰月</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1117</th>\n",
       "      <td>2023-01-22</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2023年1月1日</td>\n",
       "      <td>2023</td>\n",
       "      <td>01-01</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1118</th>\n",
       "      <td>2023-01-23</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2023年1月2日</td>\n",
       "      <td>2023</td>\n",
       "      <td>01-02</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1119</th>\n",
       "      <td>2023-01-24</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2023年1月3日</td>\n",
       "      <td>2023</td>\n",
       "      <td>01-03</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1120</th>\n",
       "      <td>2023-01-25</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2023年1月4日</td>\n",
       "      <td>2023</td>\n",
       "      <td>01-04</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1121</th>\n",
       "      <td>2023-01-26</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2023年1月5日</td>\n",
       "      <td>2023</td>\n",
       "      <td>01-05</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1496</th>\n",
       "      <td>2024-02-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2023年12月26日</td>\n",
       "      <td>2023</td>\n",
       "      <td>12-26</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1497</th>\n",
       "      <td>2024-02-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2023年12月27日</td>\n",
       "      <td>2023</td>\n",
       "      <td>12-27</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1498</th>\n",
       "      <td>2024-02-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2023年12月28日</td>\n",
       "      <td>2023</td>\n",
       "      <td>12-28</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1499</th>\n",
       "      <td>2024-02-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2023年12月29日</td>\n",
       "      <td>2023</td>\n",
       "      <td>12-29</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1500</th>\n",
       "      <td>2024-02-09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农历2023年12月30日</td>\n",
       "      <td>2023</td>\n",
       "      <td>12-30</td>\n",
       "      <td>否</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>384 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             日期 节日名称             农历  农历年份   农历月日 是否闰月\n",
       "1117 2023-01-22   春节    农历2023年1月1日  2023  01-01    否\n",
       "1118 2023-01-23   春节    农历2023年1月2日  2023  01-02    否\n",
       "1119 2023-01-24   春节    农历2023年1月3日  2023  01-03    否\n",
       "1120 2023-01-25   春节    农历2023年1月4日  2023  01-04    否\n",
       "1121 2023-01-26   春节    农历2023年1月5日  2023  01-05    否\n",
       "...         ...  ...            ...   ...    ...  ...\n",
       "1496 2024-02-05  NaN  农历2023年12月26日  2023  12-26    否\n",
       "1497 2024-02-06  NaN  农历2023年12月27日  2023  12-27    否\n",
       "1498 2024-02-07  NaN  农历2023年12月28日  2023  12-28    否\n",
       "1499 2024-02-08  NaN  农历2023年12月29日  2023  12-29    否\n",
       "1500 2024-02-09  NaN  农历2023年12月30日  2023  12-30    否\n",
       "\n",
       "[384 rows x 6 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 农历对照。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = ZhCalendarPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 数据筛选。\n",
    "# --------------------------------------------------\n",
    "CmpCalendar2022PDF = SourceDataPDF[(SourceDataPDF[\"农历年份\"] == \"2022\")]\n",
    "CmpCalendar2023PDF = SourceDataPDF[(SourceDataPDF[\"农历年份\"] == \"2023\")]\n",
    "\n",
    "# ##################################################\n",
    "CmpCalendar2023PDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f631b971",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>部门</th>\n",
       "      <th>体系</th>\n",
       "      <th>主机销量</th>\n",
       "      <th>主机销量(AVG30)</th>\n",
       "      <th>主机销量倍率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>189</td>\n",
       "      <td>189.000000</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2022-01-02</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>122</td>\n",
       "      <td>155.500000</td>\n",
       "      <td>0.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2022-01-03</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>85</td>\n",
       "      <td>132.000000</td>\n",
       "      <td>0.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2022-01-04</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>61</td>\n",
       "      <td>114.250000</td>\n",
       "      <td>0.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>2022-01-05</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>58</td>\n",
       "      <td>103.000000</td>\n",
       "      <td>0.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11602</th>\n",
       "      <td>2023-12-27</td>\n",
       "      <td>一部</td>\n",
       "      <td>综合</td>\n",
       "      <td>18</td>\n",
       "      <td>15.800000</td>\n",
       "      <td>1.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11618</th>\n",
       "      <td>2023-12-28</td>\n",
       "      <td>一部</td>\n",
       "      <td>综合</td>\n",
       "      <td>16</td>\n",
       "      <td>15.900000</td>\n",
       "      <td>1.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11634</th>\n",
       "      <td>2023-12-29</td>\n",
       "      <td>一部</td>\n",
       "      <td>综合</td>\n",
       "      <td>19</td>\n",
       "      <td>16.166667</td>\n",
       "      <td>1.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11650</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>一部</td>\n",
       "      <td>综合</td>\n",
       "      <td>28</td>\n",
       "      <td>16.733333</td>\n",
       "      <td>1.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11666</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>一部</td>\n",
       "      <td>综合</td>\n",
       "      <td>29</td>\n",
       "      <td>17.033333</td>\n",
       "      <td>1.70</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11680 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              日期  部门  体系  主机销量  主机销量(AVG30)  主机销量倍率\n",
       "0     2022-01-01  一部  华为   189   189.000000    1.00\n",
       "16    2022-01-02  一部  华为   122   155.500000    0.78\n",
       "32    2022-01-03  一部  华为    85   132.000000    0.64\n",
       "48    2022-01-04  一部  华为    61   114.250000    0.53\n",
       "64    2022-01-05  一部  华为    58   103.000000    0.56\n",
       "...          ...  ..  ..   ...          ...     ...\n",
       "11602 2023-12-27  一部  综合    18    15.800000    1.14\n",
       "11618 2023-12-28  一部  综合    16    15.900000    1.01\n",
       "11634 2023-12-29  一部  综合    19    16.166667    1.18\n",
       "11650 2023-12-30  一部  综合    28    16.733333    1.67\n",
       "11666 2023-12-31  一部  综合    29    17.033333    1.70\n",
       "\n",
       "[11680 rows x 6 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 部门销量(AVG30)。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = StoreQtyPDF.copy()\n",
    "\n",
    "# 类型转换。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF[\"日期\"] = SourceDataPDF[\"日期\"].astype(\"datetime64[ns]\")\n",
    "\n",
    "# 数据聚合。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = SourceDataPDF\n",
    "# ..................................................\n",
    "TEMPPDF = TEMPPDF.groupby([\"日期\", \"部门\", \"体系\"], as_index=False).sum(\"主机销量\")\n",
    "# ..................................................\n",
    "StoreAvg30QtyPDF = TEMPPDF\n",
    "\n",
    "# 分组计算。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = StoreAvg30QtyPDF\n",
    "# ..................................................\n",
    "Grouped = TEMPPDF.sort_values([\"体系\", \"部门\", \"日期\"])\n",
    "# ..................................................\n",
    "TEMPPDF[\"主机销量(AVG30)\"] = Grouped[\"主机销量\"].rolling(window=30, min_periods=1).mean(numeric_only=True)\n",
    "# ..................................................\n",
    "StoreAvg30QtyPDF = TEMPPDF\n",
    "\n",
    "# 数据计算。\n",
    "# --------------------------------------------------\n",
    "StoreAvg30QtyPDF[\"主机销量倍率\"] = StoreAvg30QtyPDF[\"主机销量\"] / StoreAvg30QtyPDF[\"主机销量(AVG30)\"]\n",
    "# ..................................................\n",
    "StoreAvg30QtyPDF[\"主机销量倍率\"] = round(StoreAvg30QtyPDF[\"主机销量倍率\"], 2)\n",
    "\n",
    "# ##################################################\n",
    "StoreAvg30QtyPDF.sort_values([\"体系\", \"部门\", \"日期\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "32028e7a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>节日名称</th>\n",
       "      <th>农历</th>\n",
       "      <th>是否闰月</th>\n",
       "      <th>部门</th>\n",
       "      <th>体系</th>\n",
       "      <th>主机销量</th>\n",
       "      <th>主机销量(AVG30)</th>\n",
       "      <th>主机销量倍率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2022年1月1日</td>\n",
       "      <td>否</td>\n",
       "      <td>三部</td>\n",
       "      <td>华为</td>\n",
       "      <td>26.0</td>\n",
       "      <td>49.733333</td>\n",
       "      <td>0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2022年1月1日</td>\n",
       "      <td>否</td>\n",
       "      <td>三部</td>\n",
       "      <td>形象</td>\n",
       "      <td>120.0</td>\n",
       "      <td>149.766667</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2022年1月1日</td>\n",
       "      <td>否</td>\n",
       "      <td>四部</td>\n",
       "      <td>华为</td>\n",
       "      <td>34.0</td>\n",
       "      <td>56.633333</td>\n",
       "      <td>0.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2022年1月1日</td>\n",
       "      <td>否</td>\n",
       "      <td>四部</td>\n",
       "      <td>形象</td>\n",
       "      <td>132.0</td>\n",
       "      <td>135.800000</td>\n",
       "      <td>0.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022-02-01</td>\n",
       "      <td>春节</td>\n",
       "      <td>农历2022年1月1日</td>\n",
       "      <td>否</td>\n",
       "      <td>贵州</td>\n",
       "      <td>华为</td>\n",
       "      <td>51.0</td>\n",
       "      <td>62.133333</td>\n",
       "      <td>0.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5497</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>元旦</td>\n",
       "      <td>农历2023年11月19日</td>\n",
       "      <td>否</td>\n",
       "      <td>四部</td>\n",
       "      <td>华为</td>\n",
       "      <td>83.0</td>\n",
       "      <td>52.933333</td>\n",
       "      <td>1.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5498</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>元旦</td>\n",
       "      <td>农历2023年11月19日</td>\n",
       "      <td>否</td>\n",
       "      <td>四部</td>\n",
       "      <td>形象</td>\n",
       "      <td>107.0</td>\n",
       "      <td>63.233333</td>\n",
       "      <td>1.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5501</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>元旦</td>\n",
       "      <td>农历2023年11月19日</td>\n",
       "      <td>否</td>\n",
       "      <td>贵州</td>\n",
       "      <td>华为</td>\n",
       "      <td>63.0</td>\n",
       "      <td>36.966667</td>\n",
       "      <td>1.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5502</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>元旦</td>\n",
       "      <td>农历2023年11月19日</td>\n",
       "      <td>否</td>\n",
       "      <td>贵州</td>\n",
       "      <td>形象</td>\n",
       "      <td>321.0</td>\n",
       "      <td>167.566667</td>\n",
       "      <td>1.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5503</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>元旦</td>\n",
       "      <td>农历2023年11月19日</td>\n",
       "      <td>否</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>205.0</td>\n",
       "      <td>107.966667</td>\n",
       "      <td>1.90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4893 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              日期 节日名称             农历 是否闰月  部门  体系   主机销量  主机销量(AVG30)  主机销量倍率\n",
       "3     2022-02-01   春节    农历2022年1月1日    否  三部  华为   26.0    49.733333    0.52\n",
       "4     2022-02-01   春节    农历2022年1月1日    否  三部  形象  120.0   149.766667    0.80\n",
       "9     2022-02-01   春节    农历2022年1月1日    否  四部  华为   34.0    56.633333    0.60\n",
       "10    2022-02-01   春节    农历2022年1月1日    否  四部  形象  132.0   135.800000    0.97\n",
       "13    2022-02-01   春节    农历2022年1月1日    否  贵州  华为   51.0    62.133333    0.82\n",
       "...          ...  ...            ...  ...  ..  ..    ...          ...     ...\n",
       "5497  2023-12-31   元旦  农历2023年11月19日    否  四部  华为   83.0    52.933333    1.57\n",
       "5498  2023-12-31   元旦  农历2023年11月19日    否  四部  形象  107.0    63.233333    1.69\n",
       "5501  2023-12-31   元旦  农历2023年11月19日    否  贵州  华为   63.0    36.966667    1.70\n",
       "5502  2023-12-31   元旦  农历2023年11月19日    否  贵州  形象  321.0   167.566667    1.92\n",
       "5503  2023-12-31   元旦  农历2023年11月19日    否  重庆  形象  205.0   107.966667    1.90\n",
       "\n",
       "[4893 rows x 9 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 门店同比销量(农历)。\n",
    "# --------------------------------------------------\n",
    "StoreYOYQtyPDF = StoreAvg30QtyPDF\n",
    "\n",
    "# 类型转换。\n",
    "# --------------------------------------------------\n",
    "StoreYOYQtyPDF[\"部门\"] = StoreYOYQtyPDF[\"部门\"].astype(\"string\")\n",
    "\n",
    "# 连接数据: 对应2022年数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = CmpCalendar2022PDF[[\"日期\", \"节日名称\", \"农历\", \"是否闰月\"]]\n",
    "RIGHPDF = StoreAvg30QtyPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=\"日期\")\n",
    "# ..................................................\n",
    "TEMPPDF = TEMPPDF[TEMPPDF[\"部门\"].str.contains(\"三部|四部|贵州|重庆\")]\n",
    "# ..................................................\n",
    "StoreYOYQty2022PDF = TEMPPDF\n",
    "\n",
    "# 连接数据: 对应2023年数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = CmpCalendar2023PDF[[\"日期\", \"节日名称\", \"农历\", \"是否闰月\"]]\n",
    "RIGHPDF = StoreAvg30QtyPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=\"日期\")\n",
    "# ..................................................\n",
    "TEMPPDF = TEMPPDF[TEMPPDF[\"部门\"].str.contains(\"三部|四部|贵州|重庆\")]\n",
    "# ..................................................\n",
    "StoreYOYQty2023PDF = TEMPPDF\n",
    "\n",
    "# 类型转换。\n",
    "# --------------------------------------------------\n",
    "StoreYOYQty2022PDF[\"日期\"] = StoreYOYQty2022PDF[\"日期\"].dt.strftime(\"%Y-%m-%d\")\n",
    "StoreYOYQty2023PDF[\"日期\"] = StoreYOYQty2023PDF[\"日期\"].dt.strftime(\"%Y-%m-%d\")\n",
    "\n",
    "# 合并数据。\n",
    "# --------------------------------------------------\n",
    "StoreYOYQtyPDF = pd.concat(objs=[StoreYOYQty2022PDF, StoreYOYQty2023PDF], join=\"outer\")\n",
    "\n",
    "# 保存数据。\n",
    "# --------------------------------------------------\n",
    "StoreYOYQtyPDF.to_excel(\"./Demo.xlsx\", index=False)\n",
    "\n",
    "# ##################################################\n",
    "StoreYOYQtyPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf16bd78",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
